﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using GoShopping.Model;
using GoShopping.Model.CustomDefine;
using System.Data.Common;

namespace GoShopping.BLL
{
    /// <summary>
    /// 商品操作类
    /// [使用新的事务过程]
    /// </summary>
    public class ProductBean
    {
        #region 管理员操作,商品的维护

        /// <summary>
        /// 维护商品的库存
        /// </summary>
        /// <param name="count">需要修正的数量</param>
        /// <returns></returns>
        public bool UpdateProductCount(int productID, int count)
        {
            int oldcount = Databases.DBGoShopping.Select(GoShoppingNBear.Product,
                GoShoppingNBear.Product.Count).Where(
                GoShoppingNBear.Product.ID == productID).ToScalar<int>();
            int i = Databases.DBGoShopping.Update(
                GoShoppingNBear.Product).AddColumn(
                GoShoppingNBear.Product.Count, oldcount + count).Where(
                GoShoppingNBear.Product.ID == productID).Execute();
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 根据商品ID删除商品
        /// </summary>
        /// <param name="prdctID"></param>
        /// <returns></returns>
        public bool DeleteProduct(int prdctID)
        {
            bool rt = false; ;
            DbTransaction trans = Databases.DBGoShopping.BeginTransaction();
            try
            {
                Product p = this.GetProduct(prdctID);
                //删除商品基本属性
                int i = Databases.DBGoShopping.Delete(GoShoppingNBear.Product).Where(
                    GoShoppingNBear.Product.ID == prdctID).SetTransaction(trans).Execute();
                if (i < 1)
                {
                    trans.Rollback();
                    return false;
                }

                ProductTypeBean tb = new ProductTypeBean();
                ProductType pt = tb.GetProductType(p.ProductTypeID);
                //删除商品详细属性
                StringBuilder sb = new StringBuilder();
                sb.Append("DELETE FROM ");
                sb.Append(pt.DynamicTableName);
                sb.Append(" WHERE ID=");
                sb.Append(p.ID);
                sb.Append(";");
                Databases.DBGoShopping.ExecuteNonQuery(trans, CommandType.Text, sb.ToString());

                rt = true;
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
                throw;
            }
            finally
            {
                Databases.DBGoShopping.CloseConnection(trans);
            }
            return rt;
        }

        /// <summary>
        /// 添加一个商品的基本属性
        /// </summary>
        /// <remarks>Brand品牌字段直接写Name就行了，不需要取品牌字典中的ID</remarks>
        /// <param name="prdct"></param>
        /// <returns>返回添加商品的ID</returns>
        public int AddProductBase(Product prdct)
        {
            int typeCount = Databases.DBGoShopping.Select(GoShoppingNBear.ProductType, GoShoppingNBear.ProductType.ID.Count()).Where(
                GoShoppingNBear.ProductType.ID == prdct.ProductTypeID).ToScalar<int>();
            if (typeCount != 1)
            {
                throw (new Exception("ProductType is Null!"));
            }

            int id = Databases.DBGoShopping.Insert(GoShoppingNBear.Product).AddColumn(
               GoShoppingNBear.Product.Brand, prdct.Brand).AddColumn(
               GoShoppingNBear.Product.PCode, prdct.PCode).AddColumn(
               GoShoppingNBear.Product.BriefIntroduction, prdct.BriefIntroduction).AddColumn(
               GoShoppingNBear.Product.ClickCounts, prdct.ClickCounts).AddColumn(
               GoShoppingNBear.Product.Count, prdct.Count).AddColumn(
               GoShoppingNBear.Product.IsShowOnsale, prdct.IsShowOnsale).AddColumn(
               GoShoppingNBear.Product.Name, prdct.Name).AddColumn(
               GoShoppingNBear.Product.ProductTypeID, prdct.ProductTypeID).AddColumn(
               GoShoppingNBear.Product.Rank, prdct.Rank).AddColumn(
               GoShoppingNBear.Product.SupplyerID, prdct.SupplyerID).AddColumn(
               GoShoppingNBear.Product.Price, prdct.Price).AddColumn(
               GoShoppingNBear.Product.PriceMarket, prdct.PriceMarket).AddColumn(
               GoShoppingNBear.Product.Tag, prdct.Tag).ExecuteReturnAutoIncrementID(GoShoppingNBear.Product.ID);
            return id;
        }

        /// <summary>
        /// 添加或者更新一个商品详细属性
        /// </summary>
        /// <param name="prdcuID"></param>
        /// <param name="_PropertyValueList"></param>
        /// <returns></returns>
        public void AddOrUpdateProductDetailInfo(int prdcuID, IList<ProductPropertyValueList> _PropertyValueList)
        {
            DbTransaction trans = Databases.DBGoShopping.BeginTransaction();
            try
            {
                if (_PropertyValueList.Count > 0)
                {
                    ProductTypeBean ptb = new ProductTypeBean();
                    ProductType t = ptb.GetProductType(prdcuID);
                    string typeCHName = t.TypeName;
                    string typeTableName = t.DynamicTableName;

                    //先删除
                    string sqlDel = string.Format("DELETE FROM {0} WHERE ID={1};", typeTableName, prdcuID);
                    Databases.DBGoShopping.CustomSql(sqlDel).SetTransaction(trans).ExecuteNonQuery();

                    StringBuilder sbSQL = new StringBuilder();
                    sbSQL.Append("INSERT INTO ");
                    sbSQL.Append(typeTableName);
                    sbSQL.Append("(ID,");
                    foreach (ProductPropertyValueList prpt in _PropertyValueList)
                    {
                        sbSQL.Append(prpt.PropertyColumnName).Append(",");
                    }
                    sbSQL.Remove(sbSQL.Length - 1, 1);
                    sbSQL.Append(") VALUES(");
                    sbSQL.Append(prdcuID + ",");
                    foreach (ProductPropertyValueList prpt in _PropertyValueList)
                    {
                        sbSQL.Append("'").Append(prpt.PropertyValue).Append("',");
                    }
                    sbSQL.Remove(sbSQL.Length - 1, 1);
                    sbSQL.Append(")");
                    sbSQL.Append(";");

                    Databases.DBGoShopping.CustomSql(sbSQL.ToString()).SetTransaction(trans).ExecuteNonQuery();
                    trans.Commit();
                }
            }
            catch
            {
                trans.Rollback();
                throw;
            }
            finally
            {
                Databases.DBGoShopping.CloseConnection(trans);
            }
        }

        /// <summary>
        /// 更新一个商品的排名
        /// </summary>
        /// <param name="productID"></param>
        /// <param name="newRank"></param>
        /// <returns></returns>
        public bool UpdateProductRank(int productID, int newRank)
        {
            int i = Databases.DBGoShopping.Update(GoShoppingNBear.Product).AddColumn(
               GoShoppingNBear.Product.Rank, newRank).Where(
                GoShoppingNBear.Product.ID == productID).Execute();
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新一个商品是否在货架显示
        /// </summary>
        /// <param name="productID"></param>
        /// <param name="isOnSale"></param>
        /// <returns></returns>
        public bool UpdateProductShowOnSale(int productID, bool isOnSale)
        {
            int i = Databases.DBGoShopping.Update(GoShoppingNBear.Product).AddColumn(
                GoShoppingNBear.Product.IsShowOnsale, isOnSale).Where(
                GoShoppingNBear.Product.ID == productID).Execute();
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新一个商品的基本属性
        /// </summary>
        /// <remarks>商品类型不能更新，要修改一个商品的类型必须上出这个商品，再进行添加</remarks>
        /// <param name="p"></param>
        public bool UpdateProductBaseInfo(Product p)
        {
            int i = Databases.DBGoShopping.Update(GoShoppingNBear.Product).AddColumn(
                GoShoppingNBear.Product.PCode,p.PCode).AddColumn(
                GoShoppingNBear.Product.Brand, p.Brand).AddColumn(
                GoShoppingNBear.Product.BriefIntroduction, p.BriefIntroduction).AddColumn(
                GoShoppingNBear.Product.ClickCounts, p.ClickCounts).AddColumn(
                GoShoppingNBear.Product.Count, p.Count).AddColumn(
                GoShoppingNBear.Product.IsShowOnsale, p.IsShowOnsale).AddColumn(
                GoShoppingNBear.Product.Name, p.Name).AddColumn(
                GoShoppingNBear.Product.Accessory, p.Accessory).AddColumn(
                GoShoppingNBear.Product.Rank, p.Rank).AddColumn(
                GoShoppingNBear.Product.SupplyerID, p.SupplyerID).AddColumn(
                GoShoppingNBear.Product.Tag, p.Tag).Where(
                GoShoppingNBear.Product.ID == p.ID).Execute();
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        //public bool UpdateProductDetailInfo(int prdcuID, IList<ProductPropertyValueList> _PropertyValueList)
        //{
        //    using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required))
        //    {
        //        if (_PropertyValueList.Count > 0)
        //        {
        //            ProductTypeBean ptb = new ProductTypeBean();
        //            ProductType t = ptb.GetProductType(prdcuID);
        //            string typeCHName = t.TypeName;
        //            string typeTableName = t.DynamicTableName;

        //            //先删除
        //            string sqlDel = string.Format("DELETE FROM {0} WHERE ID={1};", typeTableName, prdcuID);
        //            Databases.DBGoShopping.CustomSql(sqlDel).SetTransaction(trans).ExecuteNonQuery();

        //            StringBuilder sbSQL = new StringBuilder();
        //            sbSQL.Append("INSERT INTO ");
        //            sbSQL.Append(typeTableName);
        //            sbSQL.Append("(");
        //            foreach (ProductPropertyValueList prpt in _PropertyValueList)
        //            {
        //                sbSQL.Append(prpt.PropertyName).Append(",");
        //            }
        //            sbSQL.Remove(sbSQL.Length - 1, 1);
        //            sbSQL.Append(") VALUES(");
        //            foreach (ProductPropertyValueList prpt in _PropertyValueList)
        //            {
        //                sbSQL.Append("'").Append(prpt.PropertyValue).Append("',");
        //            }
        //            sbSQL.Remove(sbSQL.Length - 1, 1);
        //            sbSQL.Append(")");
        //            sbSQL.Append(";");

        //            Databases.DBGoShopping.CustomSql(sbSQL.ToString()).SetTransaction(trans).ExecuteNonQuery();
        //        }
        //        scope.Complete();
        //    }
        //}
        #endregion

        #region 查询商品

        /// <summary>
        /// 查询一个商品的基本属性
        /// </summary>
        /// <param name="prdctID"></param>
        /// <returns></returns>
        public Product GetProduct(int prdctID)
        {
            Product p = Databases.DBGoShopping.Select(GoShoppingNBear.Product).Where(
                GoShoppingNBear.Product.ID == prdctID).ToSingleObject<Product>();
            return p;
        }

        public Product GetProduct(string pName)
        {
            return Databases.DBGoShopping.Select(GoShoppingNBear.Product).Where(
                GoShoppingNBear.Product.Name == pName).ToSingleObject<Product>();
        }

        /// <summary>
        /// 查询一个商品的详细属性
        /// </summary>
        /// <param name="prdctID"></param>
        /// <returns></returns>
        public IList<ProductPropertyValueList> GetPrdctPropertyValueList(int prdctID)
        {
            Product p = GetProduct(prdctID);
            ProductTypeBean tb = new ProductTypeBean();
            ProductType pType = tb.GetProductType(p.ProductTypeID);

            IList<ProductPropertyValueList> rt = tb.GetProductTypePrptValue(p.ProductTypeID);
            //根据属性列表组织查找该商品的SQL
            if (rt.Count > 0)
            {
                string sql = string.Format("SELECT * FROM {0} WHERE ID =;", pType.DynamicTableName, p.ID);
                DataTable dt = Databases.DBGoShopping.CustomSql(sql).ToDataSet().Tables[0];
                if (dt.Rows.Count > 0)
                {
                    foreach (ProductPropertyValueList pr in rt)
                    {
                        pr.PropertyValue = dt.Rows[0][pr.PropertyColumnName].ToString();
                    }
                }
            }
            return rt;
        }

        /// <summary>
        /// 获取所有的商品
        /// </summary>
        /// <returns></returns>
        public IList<Product> GetAllProduct()
        {
            return Databases.DBGoShopping.Select(GoShoppingNBear.Product).OrderBy(GoShoppingNBear.Product.Rank.Asc).ToList<Product>();
        }

        /// <summary>
        /// 前面已经有了WHERE 1=1 
        /// </summary>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        public IList<Product> GetProductList(string whereStr)
        {
            string sql = string.Empty;
            sql = "SELECT * FROM " + GoShoppingNBear.Product.___GetTableName() + " WHERE 1=1 " + whereStr;
            return Databases.DBGoShopping.CustomSql(sql).ToList<Product>();
        }

        /// <summary>
        /// 按商品名称搜索商品
        /// </summary>
        /// <param name="searchKey">搜索的关键字</param>
        /// <returns></returns>
        public IList<Product> SearchProduct(string searchKey)
        {
            return Databases.DBGoShopping.Select(GoShoppingNBear.Product).Where(
                GoShoppingNBear.Product.Name.Like(searchKey)).ToList<Product>();
        }

        /// <summary>
        /// 搜索指定品牌的商品
        /// </summary>
        /// <param name="brand">品牌</param>
        /// <returns></returns>
        public IList<Product> SearchProductBrand(string brand)
        {
            return Databases.DBGoShopping.Select(GoShoppingNBear.Product).Where(
                GoShoppingNBear.Product.Brand.Like(brand)).ToList<Product>();
        }

        /// <summary>
        /// 获取所有的商品
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public IList<Product> GetAllProduct(int pageIndex, int pageSize)
        {
            int skipItemCount = pageSize * (pageIndex - 1);
            return Databases.DBGoShopping.Select(GoShoppingNBear.Product).SetSelectRange(pageSize, skipItemCount, GoShoppingNBear.PrdctSupplyer.ID).OrderBy(GoShoppingNBear.Product.Rank.Asc).ToList<Product>();
        }

        /// <summary>
        /// 获取所有商品的页数
        /// </summary>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public int GetAllProductPageCount(int pageSize)
        {
            int rowCount = (int)Databases.DBGoShopping.Select(GoShoppingNBear.Product, GoShoppingNBear.Product.ID.Count()).ToScalar();
            int pageCount = (rowCount % 10 == 0 ? rowCount / 10 : (rowCount / 10) + 1);
            return pageCount;
        }

        /// <summary>
        /// 根据价格范围查询商品
        /// </summary>
        /// <param name="moneyMin">价格最小区间</param>
        /// <param name="moneyMax">价格最大区间</param>
        /// <returns></returns>
        public IList<Product> GetProductByMoneyRange(Decimal moneyMin, Decimal moneyMax)
        {
            return Databases.DBGoShopping.Select(GoShoppingNBear.Product).Where(
                GoShoppingNBear.Product.Price.Between(moneyMin, moneyMax)).ToList<Product>();
        }
        #endregion

    }
}
